package fr.greta.centre.dao;


import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import javax.naming.NamingException;

import com.mysql.jdbc.Connection;

import fr.greta.centre.metier.Cursus;
import fr.greta.centre.metier.Module;
import fr.greta.centre.metier.UE;

public class UEDAOImpl implements UEDAO{

	public static void main(String[] args) throws SQLException {
		UEDAOImpl ueDao =  new UEDAOImpl();
		UE ue = new UE();
		Cursus cursus = new Cursus();
		ue.setNom("culture g�n�rale");
		ue.setCode(666);
		ue.setCoeff(3);
		ue.setCursus(cursus);
		

		ueDao.delete(4);		

	}


	@Override
	public int save (UE ue) throws SQLException {
		PreparedStatement st;
		try {
			Cursus cursus = new Cursus();
			cursus.setIdCursus(8);
			Connection conn = (Connection) Bd.getConnect();
			String sql = "INSERT INTO `centre_formation`.`ue` (nom_ue,`code`,`coefficient`,`id_cursus`) " +
					"VALUES ('"
					+ue.getNom()+"'"+","
					+ue.getCode()+","
					+ue.getCoeff()+","
					+cursus.getIdCursus()+")";
			System.out.println(sql);
			st = conn.prepareStatement(sql);
			st.executeUpdate(sql);		


		} catch (SQLException e) {	System.out.println(e.getMessage());	}
		return 0;
	}


	@Override
	public boolean delete(int idUE) throws SQLException {
		PreparedStatement st;
		UE ue = new UE();
		ue.setIdUE(3);
		try { 
			Connection conn = (Connection) Bd.getConnect();
			String sql = "DELETE FROM centre_formation.ue WHERE id_UE = " + ue.getIdUE();
			System.out.println(sql);
			st = conn.prepareStatement(sql);
			st.execute(sql);
		}catch (SQLException excp){ System.out.println(excp.getMessage());

		}

		return false;
	}

	@Override
	public UE findByKey(int idUE) throws SQLException {
		PreparedStatement st;
		UE ue = new UE();
		try {
			Connection conn = (Connection) Bd.getConnect();
			String sql = "SELECT * FROM centre_formation.ue WHERE id_UE = " + ue.getIdUE();
			st = conn.prepareStatement(sql);
			ResultSet res = st.executeQuery(sql);
			res.next();
			//faire une rquete imbriquee ou utiliser les methodes findCursusByIdUE (il faudrait faire d'abord 
			//les methodes findUEbyIdCursus et findModulebyIdUE)
			
					
			
			
		}
		catch(SQLException excp){System.out.println(excp.getMessage());
			
		}

		return ue;
	}

	@Override
	public List<UE> findAll() throws SQLException {

		return null;
	}

	public List<UE> findByIdCursus(int idCursus){
		ArrayList<UE> lesUE = new ArrayList<UE>();
		String sql = "SELECT * FROM ue WHERE id_cursus = ?";
		try {
			PreparedStatement st = Bd.getConnect().prepareStatement(sql);
			st.setInt(1, idCursus);
			ResultSet rs = st.executeQuery();
			while(rs.next()){
				Cursus cursus = new CursusDAOImpl().findByKey(rs.getInt("id_cursus"));
				List<Module> lesModules = new ModuleDAOImpl().findByIdUe(rs.getInt("id_ue"));
				UE uneUE = new UE(lesModules, cursus, rs.getString("nom_ue"), rs.getInt("coeficient"), rs.getInt("id_ue"), rs.getShort("code"));
				lesUE.add(uneUE);
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return lesUE;
	}
}
